﻿using BCCommon;
using BCEntity.Sim.Company.Department;
using Dapper;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace BCData.Sim.Company.Department
{
    public class DepartmentData : IDepartmentData
    {
        #region 私有变量

        private readonly IDatabaseContext databaseContext;

        private readonly string QUERY = @"SELECT DepartmentId,ParentDepartmentId,ParentDepartmentName,DepartmentName,PrincipalAdminId,
                                            DepartmentPrincipal,EmployeeCount,CreateTime,DepartmentCreateTime,EditerId,Editer,CompanyId,
                                            DepartmentPath,CompanySubName,CompanyName,DepartmentIntroduction
                                          FROM department";

        #endregion 私有变量

        #region 构造函数

        public DepartmentData(IDatabaseContext databaseContext)
        {
            this.databaseContext = databaseContext;
        }
        #endregion

        #region 公共方法

        public async Task<DepartmentEntity> InsertAsync(DepartmentEntity entity)
        {
            string sql = SqlCommands.GetInsertSql("Department", new[]
            {
                "ParentDepartmentId","ParentDepartmentName", "DepartmentName", "EmployeeCount", "DepartmentCreateTime", "EditerId",
                "Editer", "CompanyId","DepartmentPath","CompanySubName","CompanyName","DepartmentIntroduction"
            });
            sql = string.Format("{0} {1} WHERE DepartmentId=LAST_INSERT_ID();", sql, QUERY);
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?ParentDepartmentId", entity.ParentDepartmentId,DbType.Int32);
            parameters.Add("?ParentDepartmentName", entity.ParentDepartmentName?.Trim(), DbType.AnsiString, size: 100);
            parameters.Add("?CompanyName", entity.CompanyName?.Trim(), DbType.AnsiString, size: 50);
            parameters.Add("?DepartmentPath", entity.DepartmentPath?.Trim(), DbType.AnsiString, size: 2000);
            parameters.Add("?DepartmentCreateTime", entity.DepartmentCreateTime, DbType.DateTime);
            parameters.Add("?EditerId", entity.EditerId, DbType.Int64, size: 20);
            parameters.Add("?Editer", entity.Editer?.Trim(), DbType.AnsiString, size: 30);
            parameters.Add("?DepartmentName", entity.DepartmentName?.Trim(), DbType.AnsiString, size: 100);
            parameters.Add("?EmployeeCount", entity.EmployeeCount, DbType.Int32, size: 11);
            parameters.Add("?CompanyId", entity.CompanyId, DbType.Int32, size: 11);
            parameters.Add("?CompanySubName", entity.CompanySubName?.Trim(), DbType.AnsiString, size: 50);
            parameters.Add("?DepartmentIntroduction", entity.DepartmentIntroduction, DbType.String);
            var result = await databaseContext.Master.QueryFirstOrDefaultAsync<DepartmentEntity>(sql, parameters, databaseContext.Transaction);
            if (result != null)
            {
                sql = string.Format("UPDATE Department SET DepartmentPath=QueryParentDepartmentPath(?ParentDepartmentId,?Id) WHERE DepartmentId=?Id;");
                parameters = new DynamicParameters();
                parameters.Add("?ParentDepartmentId", entity.ParentDepartmentId, DbType.Int32);
                parameters.Add("?Id", result.DepartmentId, DbType.Int32);
                var executeResult = await databaseContext.Master.ExecuteAsync(sql, parameters,databaseContext.Transaction);
                if (executeResult == 0)
                {
                    return null;
                }
            }
            return result;

        }

        public async Task<DepartmentEntity> UpdateAsync(DepartmentEntity department)
        {
            string sql = @"SELECT COUNT(1) FROM StaffOrganizationMappingRecord A
                                INNER JOIN Department B ON A.DepartmentId=B.DepartmentId
                                WHERE FIND_IN_SET(?DepartmentId,B.DepartmentPath)";
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?DepartmentId", department.DepartmentId, DbType.Int32, size: 10);
            int staffCount = await databaseContext.Master.ExecuteScalarAsync<int>(sql, parameters);
            sql = @"UPDATE department SET
                            ParentDepartmentId=?ParentDepartmentId,
                            ParentDepartmentName=?ParentDepartmentName,
                            DepartmentName=?DepartmentName,
                            EmployeeCount=?StaffCount,
                            EditerId=?EditerId,
                            Editer=?Editer,
                            CompanyId=?CompanyId,
                            CompanySubName=?CompanySubName,
                            CompanyName=?CompanyName,
                            DepartmentIntroduction=?DepartmentIntroduction,
                            DepartmentPath=QueryParentDepartmentPath(?ParentDepartmentId,?DepartmentId)
                        WHERE DepartmentId=?DepartmentId;";
            parameters = new DynamicParameters();
            parameters.Add("?ParentDepartmentId", department.ParentDepartmentId, DbType.Int32, size: 10);
            parameters.Add("?ParentDepartmentName", department.ParentDepartmentName, DbType.AnsiString, size: 100);
            parameters.Add("?DepartmentName", department.DepartmentName, DbType.AnsiString, size: 100);
            parameters.Add("?EmployeeCount", department.EmployeeCount, DbType.Int32, size: 10);
            parameters.Add("?EditerId", department.EditerId, DbType.Int64, size: 20);
            parameters.Add("?Editer", department.Editer, DbType.AnsiString, size: 30);
            parameters.Add("?CompanyId", department.CompanyId, DbType.Int32, size: 10);
            parameters.Add("?CompanySubName", department.CompanySubName, DbType.AnsiString, size: 50);
            parameters.Add("?CompanyName", department.CompanyName, DbType.AnsiString, size: 50);
            parameters.Add("?DepartmentIntroduction", department.DepartmentIntroduction, DbType.String);
            parameters.Add("?DepartmentId", department.DepartmentId, DbType.Int32, size: 10);
            parameters.Add("?StaffCount", staffCount, DbType.Int32);
            _ = await databaseContext.Master.ExecuteAsync(sql, parameters, databaseContext.Transaction) > 0;
            sql = string.Format("{0} WHERE DepartmentId=?DepartmentId", QUERY);
            parameters = new DynamicParameters();
            parameters.Add("?DepartmentId", department.DepartmentId, DbType.Int32, size: 10);
            return await databaseContext.Master.QueryFirstOrDefaultAsync<DepartmentEntity>(sql, parameters); 
        }

        public async Task<bool> DeleteAsync(int id)
        {
            string sql = @"DELETE FROM department WHERE DepartmentId = ?DepartmentId";
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?DepartmentId", id);
            return await databaseContext.Master.ExecuteAsync(sql, parameters, databaseContext.Transaction)>0;
        }
        
        public async Task<DepartmentEntity> CheckDepartmentName(int companyId, string departmentName)
        {
            var sql = $"{QUERY} WHERE CompanyId=?CompanyId AND DepartmentName=?DepartmentName LIMIT 1";
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?CompanyId", companyId, DbType.Int32, size: 10);
            parameters.Add("?DepartmentName", departmentName, DbType.AnsiString, size: 100);
            return await databaseContext.Slave.QueryFirstOrDefaultAsync<DepartmentEntity>(sql, parameters);
        }

        public async Task<DepartmentEntity> GetById(int departmentId)
        {
            string sql = $@"{QUERY} WHERE DepartmentId=?DepartmentId LIMIT 1";
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?DepartmentId", departmentId);
            return await databaseContext.Slave.QueryFirstOrDefaultAsync<DepartmentEntity>(sql, parameters);
        }

        public async Task<DepartmentEntity> GetByPrincipalAdminId(long principalAdminId)
        {
            string sql = $@"{QUERY} WHERE PrincipalAdminId=?PrincipalAdminId LIMIT 1";
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?PrincipalAdminId", principalAdminId);
            return await databaseContext.Slave.QueryFirstOrDefaultAsync<DepartmentEntity>(sql, parameters);
        }

        public async Task<(IEnumerable<DepartmentEntity>,int dataCount)> Query(int companyId, string departmentName, DateTime? start, DateTime? end, string sortName, bool? ascending, int pageIndex, int pageSize)
        {
            List<string> wheres = new List<string>();
            DynamicParameters parameters = new DynamicParameters();

            wheres.Add("CompanyId=?CompanyId");
            parameters.Add("?CompanyId", companyId);

            //if (departmentId.HasValue)
            //{
            //    wheres.Add(@"FIND_IN_SET(?DepartmentId,DepartmentPath)>0");
            //    parameters.Add("?DepartmentId", departmentId);
            //}
            if (!string.IsNullOrWhiteSpace(departmentName))
            {
                wheres.Add("DepartmentName LIKE CONCAT('%',CONCAT(?DepartmentName,'%'))");
                parameters.Add("?DepartmentName", departmentName?.Trim(), DbType.AnsiString);
            }
            if (start.HasValue && !end.HasValue)
            {
                wheres.Add("CreateTime>=?Start");
                parameters.Add("?Start", start.Value, DbType.DateTime);
            }
            else if (!start.HasValue && end.HasValue)
            {
                wheres.Add("CreateTime<=?End");
                parameters.Add("?End", end.Value, DbType.DateTime);
            }
            else if (start.HasValue && end.HasValue)
            {
                wheres.Add("CreateTime>=?Start AND CreateTime<=?End");
                parameters.Add("?Start", start.Value, DbType.DateTime);
                parameters.Add("?End", end.Value.AddDays(1).Date, DbType.DateTime);
            }
            string sql = string.Format(@"{0} {1} ORDER BY {4} {5} LIMIT {2},{3}; SELECT COUNT(1) FROM Department {1}",
                   QUERY,
                  wheres.Count > 0 ? " WHERE " + string.Join(" AND ", wheres) : "",
                  (pageIndex == 0 ? 0 : pageIndex - 1) * pageSize,
                  pageSize,
                  string.IsNullOrWhiteSpace(sortName) ? "CreateTime" : sortName,
                  ascending.HasValue && ascending.Value ? "ASC" : "DESC");
            return await this.databaseContext.Slave.QueryMultipleAsync<DepartmentEntity, int>(sql, parameters);
        }

        public async Task<IEnumerable<DepartmentEntity>> GetAll(int companyId)
        {
            string sql = string.Format("{0} WHERE CompanyId=?CompanyId ORDER BY DepartmentId DESC", QUERY);
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?CompanyId", companyId, DbType.Int32, size: 10);
            return await databaseContext.Slave.QueryAsync<DepartmentEntity>(sql, parameters);
        }

        public async Task<IEnumerable<DepartmentPrincipalEntity>> GetStaffList(int companyId, int? departmentId)
        {
            DynamicParameters parameters = new DynamicParameters();
            string where = string.Empty;
            if (departmentId.HasValue)
            {
                where = @" AND FIND_IN_SET(?DepartmentId,A.DepartmentPath)>0";
                parameters.Add("?DepartmentId", departmentId, DbType.Int32);
            }
            string sql = @"SELECT A.DepartmentId,A.DepartmentName,A.ParentDepartmentId,A.ParentDepartmentName,A.DepartmentPath,A.CompanyId,A.CompanyName,A.DepartmentPath,
	                        B.AdminId,B.FullName,B.JobTitleKey,B.JobDutyKey,B.JobTitle,B.JobDuty
                        FROM Department A
                        INNER JOIN StaffOrganizationMappingRecord B ON A.PrincipalAdminId=B.AdminId AND A.CompanyId=B.CompanyId
                        WHERE A.CompanyId=?CompanyId {0}
                        GROUP BY B.AdminId";
            sql = string.Format(sql, where);
            parameters.Add("?CompanyId", companyId, DbType.Int32);
            return await databaseContext.Slave.QueryAsync<DepartmentPrincipalEntity>(sql, parameters);
        }

        public async Task<bool> CheckPrincipal(long principalAdminId)
        {
            var sql = "SELECT 1 FROM Department WHERE PrincipalAdminId=?PrincipalAdminId LIMIT 1";
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?PrincipalAdminId", principalAdminId);
            return await databaseContext.Slave.ExecuteScalarAsync(sql, parameters) != null;
        }

        public async Task<bool> CheckDepartment(int departmentId)
        {
            var sql = "SELECT 1 FROM department WHERE DepartmentId=?DepartmentId LIMIT 1";
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?DepartmentId", departmentId);
            return await databaseContext.Slave.ExecuteScalarAsync(sql, parameters) != null;
        }

        public async Task<IEnumerable<DepartmentEntity>> QueryChildren(int departmentId, int companyId)
        {
            DynamicParameters parameters = new DynamicParameters();
            string where = " WHERE CompanyId=?CompanyId AND ParentDepartmentId=?DepartmentId ORDER BY DepartmentId DESC";
            parameters.Add("?CompanyId", companyId);
            parameters.Add("?DepartmentId", departmentId, DbType.Int32, size: 10);
            string sql = string.Format("{0} {1}", QUERY, where);

            return await databaseContext.Slave.QueryAsync<DepartmentEntity>(sql, parameters);

        }

        public async Task<IEnumerable<DepartmentEntity>> QueryChildren(int departmentId)
        {
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?DepartmentId", departmentId, DbType.Int32, size: 10);
            string sql = string.Format(@"{0} WHERE ParentDepartmentId=?DepartmentId ORDER BY DepartmentId DESC", QUERY);

            return await this.databaseContext.Slave.QueryAsync<DepartmentEntity>(sql, parameters);
        }

        public async Task<IEnumerable<DepartmentEntity>> QueryRootDepartment(int companyId)
        {
            string sql = string.Format(@"{0} WHERE CompanyId=?CompanyId AND ParentDepartmentId IS NULL ORDER BY DepartmentId DESC", QUERY);
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?CompanyId", companyId, DbType.Int32, size: 10);
            return await this.databaseContext.Slave.QueryAsync<DepartmentEntity>(sql, parameters);
        }

        public async Task<bool> UpdateDepartmentPrincipal(int companyId, int departmentId, long adminId, string adminFullName)
        {
            string sql = SqlCommands.GetUpdateSql("Department", new string[] { "PrincipalAdminId", "DepartmentPrincipal" },
                new string[] { "CompanyId", "DepartmentId" });
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?CompanyId", companyId, DbType.Int32, size: 10);
            parameters.Add("?DepartmentId", departmentId, DbType.Int32, size: 10);
            parameters.Add("?PrincipalAdminId", adminId, DbType.Int64, size: 20);
            parameters.Add("?DepartmentPrincipal", adminFullName, DbType.AnsiString, size: 50);
            return await this.databaseContext.Master.ExecuteAsync(sql, parameters, databaseContext.Transaction) > 0;
        }
        
        public async Task<DepartmentEntity> RemovePrincipalInfo(int companyId, int departmentId)
        {
            string sql = SqlCommands.GetUpdateSql("Department", new string[] { "PrincipalAdminId", "DepartmentPrincipal" },
                  new string[] { "CompanyId", "DepartmentId" });
            sql = string.Format(@"{0} {1} WHERE CompanyId=?CompanyId AND DepartmentId=?DepartmentId ORDER BY DepartmentId DESC LIMIT 1", sql, QUERY);
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?CompanyId", companyId, DbType.Int32, size: 10);
            parameters.Add("?DepartmentId", departmentId, DbType.Int32, size: 10);
            parameters.Add("?PrincipalAdminId", null, DbType.Int64, size: 20);
            parameters.Add("?DepartmentPrincipal", null, DbType.AnsiString, size: 50);
            return await this.databaseContext.Master.QueryFirstOrDefaultAsync<DepartmentEntity>(sql, parameters);
        }


        public async Task<IEnumerable<DepartmentEntity>> GetDepartmentAndChildren(int departmentId, List<DepartmentEntity> departmentEntityList)
        {
            if (departmentEntityList == null)
                departmentEntityList = new List<DepartmentEntity>();

            departmentEntityList.Add(GetById(departmentId).GetAwaiter().GetResult());

            var list = QueryChildren(departmentId).GetAwaiter().GetResult();
            if (list != null && list.Any())
            {
                foreach (var item in list)
                {
                    await GetDepartmentAndChildren(item.DepartmentId, departmentEntityList);
                }
            }
            return departmentEntityList;
        }

        public async Task<bool> UpdateStaffCountAsync(int departmentId)
        {
            string countSql = @"SELECT COUNT(1) FROM StaffOrganizationMappingRecord A
                                INNER JOIN Department B ON A.DepartmentId=B.DepartmentId
                                WHERE FIND_IN_SET(?DepartmentId,B.DepartmentPath)";
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?DepartmentId", departmentId, DbType.Int32, size: 10);
            var result = await this.databaseContext.Master.ExecuteScalarAsync<int>(countSql, parameters);
            string sql = @"UPDATE Department SET 
                                EmployeeCount=?Count
                            WHERE DepartmentId=?DepartmentId";
            parameters.Add("?DepartmentId", departmentId, DbType.Int32, size: 10);
            parameters.Add("?Count", result, DbType.Int32, size: 10);
            return await this.databaseContext.Master.ExecuteAsync(sql, parameters, databaseContext.Transaction) > 0;
        }
        #endregion 公共方法
    }
}
